begin;
drop view certificate_of_origin_pdf_view;
create or replace view certificate_of_origin_pdf_view as
select commercial_id,
 ccb.company_name as buyer,
 ccb.address as buyer_address,
 bmc.city_name as buyer_city,
 ms.state_name as buyer_state,
 bc.country_name as buyer_country,
 ccb.zipcode as buyer_zipcode,
 cca.company_name as company_id,
 cca.address as company_address,
 cmc.city_name as company_city,
 cms.state_name as company_state,
 cbc.country_name as company_country,
 cca.zipcode as company_zipcode,
 pre_carriage,
 place_of_receipt,
 sfc.city_name as from_city,
 mp1.port_name as discharge,
 mp2.port_name as destination,
 marks_and_nos,
 no_of_pkgs,
 description,
 quantity_type_id,
 "value",
 name_of_signatory,
sm.shipment_mode_name,
total_qty,
total_val
from certificate_of_origin_commercial coo
left join _mcompany jb on jb.company_id=coo.buyer_id
left join _mcompany ja on ja.company_id=coo.account_id
left join _mship_mode sm on sm.shipment_mode_id=coo.ship_mode_id
left join _mport mp1 on mp1.port_id=coo.port_dis
left join _mport mp2 on mp2.port_id=coo.port_des
left join _mcity mc on mc.city_id=coo.ship_from_city
left join company_contact ccb on ccb.company_id=coo.buyer_id
left join company_contact cca on cca.company_id=coo.account_id
left join _mcity bmc on bmc.city_id=ccb.city_name
left join _mstate ms on ms.state_id=ccb.state
left join _mcountry bc on bc.country_id=ccb.country_name
left join _mcity cmc on cmc.city_id=cca.city_name
left join _mstate cms on cms.state_id=cca.state
left join _mcountry cbc on cbc.country_id=cca.country_name
left join _mcity sfc on sfc.city_id=coo.ship_from_city;
commit;
